﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace ZX.Report
{
    /// <summary> 
    /// 对数据库操作的类，包括附加、还原、备份、分离、压缩、创建、修改等常见操作.版本1.0 
    /// 最新版本主要在命名规范上做了完善,同时增加了还原时重命名数据库功能 
    /// </summary> 
    public class DataBaseHelper
    {
        /// <summary> 
        /// 数据库连接字符串 
        /// </summary> 
        public string ConnectionString = "data source=192.168.6.200;Initial Catalog=zx_app_dev;User ID=sa;Password=123";
        /// <summary> 
        /// SQL操作语句 
        /// </summary> 
        public string StrSQL;
        /// <summary> 
        /// 数据库连接对象 
        /// </summary> 
        private SqlConnection Conn;
        /// <summary> 
        /// 数据库操作对象Comm 
        /// </summary> 
        private SqlCommand Comm;
        /// <summary> 
        /// 要操作的数据库名称 
        /// </summary> 
        public string DataBaseName;
        /// <summary> 
        /// 数据库逻辑名 
        /// 逻辑名为数据库创建后的初始文件名，以后不会随着数据库的操作(如备份还原)而改变 
        /// 此处要区别于DataBaseName 
        /// </summary> 
        public string LogicalDataBaseName;
        /// <summary> 
        /// 数据库文件完整地址 
        /// </summary> 
        public string DataBase_MDF_Address;
        /// <summary> 
        /// 数据库日志文件完整地址 
        /// </summary> 
        public string DataBase_LDF_Address;
        /// <summary> 
        /// 备份文件名 
        /// </summary> 
        public string DataBaseOfBackupName;
        /// <summary> 
        /// 备份文件路径 
        /// </summary> 
        public string DataBaseOfBackupPath;
        /// <summary> 
        /// 数据库压缩比，1%－99% 
        /// </summary> 
        public string Percent;

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns></returns>
        public bool DoConnect()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                return true;
            }
            catch
            { }
            return false;
        }

        /// <summary> 
        /// 执行创建/修改数据库和表的通用操作 
        /// </summary> 
        public void DataBaseAndTableOperate()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = StrSQL;
                Comm.CommandType = CommandType.Text;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }
        /// <summary> 
        /// 附加数据库 
        /// </summary> 
        public void AppendDataBase()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = "sp_attach_db";
                Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
                Comm.Parameters[@"dbname"].Value = DataBaseName;
                Comm.Parameters.Add(new SqlParameter(@"filename1", SqlDbType.NVarChar));
                Comm.Parameters[@"filename1"].Value = DataBase_MDF_Address;
                Comm.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));
                Comm.Parameters[@"filename2"].Value = DataBase_LDF_Address;
                Comm.CommandType = CommandType.StoredProcedure;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }
        /// <summary> 
        /// 分离数据库 
        /// </summary> 
        public void DetachDataBase()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = @"sp_detach_db";
                Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
                Comm.Parameters[@"dbname"].Value = DataBaseName;
                Comm.CommandType = CommandType.StoredProcedure;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }
        /// <summary> 
        /// 备份数据库 
        /// </summary> 
        public void BackupDataBase()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = "use master;backup database @dbname to disk = @backupname;";
                Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
                Comm.Parameters[@"dbname"].Value = DataBaseName;
                Comm.Parameters.Add(new SqlParameter(@"backupname", SqlDbType.NVarChar));
                Comm.Parameters[@"backupname"].Value = @DataBaseOfBackupPath + @DataBaseOfBackupName;

                Comm.CommandType = CommandType.Text;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();

            }
        }
        /// <summary> 
        /// 还原数据库 
        /// </summary> 
        public void RestoreDataBase()
        {
            try
            {
                string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = "use master;restore filelistonly from disk = @BackupFile;restore database @DataBaseName from disk = @BackupFile with move '" + LogicalDataBaseName + "' to '" + DataBase_MDF_Address + "',move 'ajgl_baseunit_Log' to '" + DataBase_LDF_Address + "',stats = 10, replace;";
                Comm.Parameters.Add(new SqlParameter(@"DataBaseName", SqlDbType.NVarChar));
                Comm.Parameters[@"DataBaseName"].Value = DataBaseName;
                Comm.Parameters.Add(new SqlParameter(@"BackupFile", SqlDbType.NVarChar));
                Comm.Parameters[@"BackupFile"].Value = BackupFile;
                Comm.CommandType = CommandType.Text;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }
        /// <summary> 
        /// 压缩数据库 
        /// </summary> 
        public void CompressDatabase()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = "DBCC SHRINKDATABASE (" + DataBaseName + "," + Percent + ")";
                Comm.CommandType = CommandType.Text;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }
        /// <summary> 
        /// 设置数据库为只读 
        /// </summary> 
        /// <param name=""></param> 
        public void ReadOnlyDatabase()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = "USE master; EXEC sp_dboption '" + DataBaseName + "', 'read only', 'TRUE'";

                Comm.CommandType = CommandType.Text;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }
        /// <summary> 
        /// 设置数据库为脱机状态 
        /// </summary> 
        public void OfflineDatabase()
        {
            try
            {
                Conn = new SqlConnection(ConnectionString);
                Conn.Open();
                Comm = new SqlCommand();
                Comm.Connection = Conn;
                Comm.CommandText = "USE master; EXEC sp_dboption '" + DataBaseName + "', 'offline', 'TRUE'";

                Comm.CommandType = CommandType.Text;
                Comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                Conn.Close();
            }
        }

        /// <summary>
        /// 查询sql中的非系统数据库
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<string> DatabaseList()
        {
            List<string> getCataList = new List<string>();
            string cmdStirng = "select name from sys.databases where database_id > 4";
            SqlConnection connect = new SqlConnection(this.ConnectionString);
            SqlCommand cmd = new SqlCommand(cmdStirng, connect);
            try
            {
                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                    IDataReader dr = cmd.ExecuteReader();
                    getCataList.Clear();
                    while (dr.Read())
                    {
                        getCataList.Add(dr["name"].ToString());
                    }
                    dr.Close();
                }

            }
            catch (SqlException e)
            {
                //MessageBox.Show(e.Message);
            }
            finally
            {
                if (connect != null && connect.State == ConnectionState.Open)
                {
                    connect.Dispose();
                }
            }
            return getCataList;
        }

        /// <summary>
        /// 获取表名
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<string> GetTables()
        {
            List<string> tablelist = new List<string>();
            SqlConnection objConnetion = new SqlConnection(this.ConnectionString);
            try
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                    DataTable objTable = objConnetion.GetSchema("Tables");
                    foreach (DataRow row in objTable.Rows)
                    {
                        tablelist.Add(row[2].ToString());
                    }
                }
            }
            catch
            {
            }
            finally
            {
                if (objConnetion != null && objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Dispose();
                }

            }
            return tablelist;
        }

        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> GetColumnField(string TableName)
        {
            List<string> Columnlist = new List<string>();
            SqlConnection objConnetion = new SqlConnection(this.ConnectionString);
            try
            {
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }

                SqlCommand cmd = new SqlCommand("Select Name FROM SysColumns Where id=Object_Id('" + TableName + "')", objConnetion);
                SqlDataReader objReader = cmd.ExecuteReader();

                while (objReader.Read())
                {
                    Columnlist.Add(objReader[0].ToString());

                }
            }
            catch
            {

            }
            objConnetion.Close();
            return Columnlist;
        }

        public SqlDataReader GetDataReaderBySql(string sql)
        {
            SqlConnection objConnetion = null;
            SqlCommand cmd = null;
            SqlDataReader objReader = null;
            try
            {
                objConnetion = new SqlConnection(this.ConnectionString);
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }
                cmd = new SqlCommand(sql, objConnetion);
                objReader = cmd.ExecuteReader();
                return objReader;
            }
            catch
            { }
            return null;
        }

        /// <summary>
        /// 获取数据值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetValueBySql(string sql)
        {
            SqlConnection objConnetion = null;
            SqlCommand cmd = null;
            SqlDataReader objReader = null;
            try
            {
                objConnetion = new SqlConnection(this.ConnectionString);
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }
                cmd = new SqlCommand(sql, objConnetion);
                objReader = cmd.ExecuteReader();
                if (objReader.Read())
                {
                    return objReader[0].ToString();
                }
            }
            catch
            { }
            finally
            {
                if (objReader != null) objReader.Close();
                if (objConnetion != null && objConnetion.State == ConnectionState.Open) objConnetion.Close();
            }
            return null;
        }

        public List<string> GetListValueBySql(string sql)
        {
            SqlConnection objConnetion = null;
            SqlCommand cmd = null;
            SqlDataReader objReader = null;
            List<string> list = null;
            try
            {
                list = new List<string>();
                objConnetion = new SqlConnection(this.ConnectionString);
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }
                cmd = new SqlCommand(sql, objConnetion);
                objReader = cmd.ExecuteReader();
                while (objReader.Read())
                {
                    list.Add(objReader[0].ToString());
                }
            }
            catch
            { }
            finally
            {
                if (objReader != null) objReader.Close();
                if (objConnetion != null && objConnetion.State == ConnectionState.Open) objConnetion.Close();
            }
            return list;
        }

        /// <summary>
        /// 获取实体数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDatatableBySql(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter oda = new SqlDataAdapter(cmd))
                    {
                        oda.Fill(ds);
                    }
                }
                catch
                { }
                finally
                {
                    if (conn != null && conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取实体数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySql(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter oda = new SqlDataAdapter(cmd))
                    {
                        oda.Fill(ds);
                    }
                }
                catch
                { }
                finally
                {
                    if (conn != null && conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql)
        {
            SqlConnection objConnetion = null;
            SqlCommand cmd = null;
            try
            {
                objConnetion = new SqlConnection(this.ConnectionString);
                if (objConnetion.State == ConnectionState.Closed)
                {
                    objConnetion.Open();
                }
                cmd = new SqlCommand(sql, objConnetion);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (objConnetion != null && objConnetion.State == ConnectionState.Open) objConnetion.Close();
            }
            return -1;
        }
    }
}